# -*- coding: utf-8 -*-
# 作者公众号：程序员张先生
import os
import time

import openpyxl
import xlrd

SPLIT_STR = "|"

# 管控excel需要用的主要sheet页
GUANKONG_SHEET_NEEDED = ["作业流管控定义", "作业流调度定义", "作业管控定义", "作业调度定义", "作业调度依赖信息（全量，如有必填）", "作业流调度依赖定义(全量，如有必填)"]

# 作业流名称在每个sheet页所在的列位置
JOBFLOW_INDEX_DICT = {}
JOBFLOW_INDEX_DICT["作业流管控定义"] = 1
JOBFLOW_INDEX_DICT["作业流调度定义"] = 1
JOBFLOW_INDEX_DICT["作业流调度依赖定义(全量，如有必填)"] = 1
JOBFLOW_INDEX_DICT["作业流调度依赖定义(全量，如有必填)"] = 1
JOBFLOW_INDEX_DICT["作业管控定义"] = 5
JOBFLOW_INDEX_DICT["作业调度定义"] = 1
JOBFLOW_INDEX_DICT["作业调度依赖信息（全量，如有必填）"] = 1

# 作业名称在每个sheet页所在的列位置
JOB_INDEX_DICT = {}
JOB_INDEX_DICT["作业管控定义"] = 1
JOB_INDEX_DICT["作业调度定义"] = 2
JOB_INDEX_DICT["作业调度依赖信息（全量，如有必填）"] = 2


def get_jobflow_list(excel_file, job_list, sheet_name):
    jobflow_list = []
    book = openpyxl.load_workbook(excel_file)
    sheet = book[sheet_name]
    jobflow_column = JOBFLOW_INDEX_DICT[sheet_name]  # 作业流字段所在列位置
    target_column = JOB_INDEX_DICT[sheet_name]
    row_count = sheet.max_row
    for i in range(1, row_count + 1):
        if sheet.cell(i, target_column).value in job_list:
            jobflow_list.append(sheet.cell(i, jobflow_column).value)
    return jobflow_list


def filter_by_job(excel_file, job_str, excel_only_header):
    job_list = job_str.split(SPLIT_STR)
    # 通过作业名称列表，获取对应的作业流列表名称
    jobflow_list = get_jobflow_list(excel_file, job_list, "作业管控定义")

    sheet_name_list = GUANKONG_SHEET_NEEDED
    new_book = openpyxl.Workbook()
    source_book = openpyxl.load_workbook(excel_file)

    not_found = True
    for sheet_name in sheet_name_list:
        new_sheet = new_book.create_sheet(sheet_name)
        source_sheet = source_book[sheet_name]
        start_row = 4  # 管控EXCEL特点，该sheet的第4行起的数据才是真实数据
        row_count = source_sheet.max_row
        col_count = source_sheet.max_column

        if "作业流" in sheet_name:
            # 当前处理的是作业流相关的sheet页
            target_column = JOBFLOW_INDEX_DICT[sheet_name]  # 作业流字段所在列位置
            target_list = jobflow_list
        else:
            target_column = JOB_INDEX_DICT[sheet_name]  # 作业字段所在列位置
            target_list = job_list

        new_sheet_start_row = 1
        for i in range(start_row, row_count + 1):
            if source_sheet.cell(i, target_column).value in target_list:
                for j in range(1, col_count + 1):
                    new_sheet.cell(new_sheet_start_row, j, source_sheet.cell(i, j).value)
                new_sheet_start_row = new_sheet_start_row + 1
                not_found = False
    if not_found:
        print("在原excel中未找到：" + job_str)
    else:
        result_file_name = "output/after_filter_by_job_" + str(
            time.strftime("%Y%m%d%H%M%S", time.localtime())) + "_without_header" + ".xlsx"
        tips = "本工具目前版本，筛选生成的excel，不携带原样式。\n生成的不带表头的excel见："
        generate_result_file(result_file_name, new_book, tips)
        generate_excel_with_header(excel_only_header, result_file_name, sheet_name_list)


def filter_by_jobflow(excel_file, job_flow_str, excel_only_header):
    target_list = job_flow_str.split(SPLIT_STR)
    sheet_name_list = GUANKONG_SHEET_NEEDED
    new_book = openpyxl.Workbook()
    source_book = openpyxl.load_workbook(excel_file)

    not_found = True
    for sheet_name in sheet_name_list:
        new_sheet = new_book.create_sheet(sheet_name)
        source_sheet = source_book[sheet_name]
        start_row = 4  # 管控EXCEL特点，该sheet的第4行起的数据才是真实数据
        row_count = source_sheet.max_row
        col_count = source_sheet.max_column
        target_column = JOBFLOW_INDEX_DICT[sheet_name]  # 作业流字段所在列位置

        new_sheet_start_row = 1
        for i in range(start_row, row_count + 1):
            if source_sheet.cell(i, target_column).value in target_list:
                for j in range(1, col_count + 1):
                    new_sheet.cell(new_sheet_start_row, j, source_sheet.cell(i, j).value)
                new_sheet_start_row = new_sheet_start_row + 1
                not_found = False
    if not_found:
        print("在原excel中未找到：" + job_flow_str)
    else:
        result_file_name = "output/after_filter_by_jobflow_" + str(
            time.strftime("%Y%m%d%H%M%S", time.localtime())) + "_without_header" + ".xlsx"
        tips = "本工具目前版本，筛选生成的excel，不携带原样式。\n生成的不带表头的excel见："
        generate_result_file(result_file_name, new_book, tips)
        generate_excel_with_header(excel_only_header, result_file_name, sheet_name_list)


# 生成带有表头的excel
# 把excel A的数据读取出来，追加到excel B(它各sheet仅带数据表头)里去。生成的excel可以保留有B的样式
def generate_excel_with_header(excel_only_header, excel_without_header, sheet_name_list):
    if excel_only_header is None:
        return
    data_book = openpyxl.load_workbook(excel_without_header)
    new_book = openpyxl.load_workbook(excel_only_header)
    for sheet_name in sheet_name_list:
        new_sheet = new_book[sheet_name]
        max_row = new_sheet.max_row
        data_sheet = data_book[sheet_name]
        row_count = data_sheet.max_row
        col_count = data_sheet.max_column
        for i in range(1, row_count + 1):
            for j in range(1, col_count + 1):
                new_sheet.cell(i + max_row, j, data_sheet.cell(i, j).value)
    result_file_name = "output/excel_with_header" + str(
        time.strftime("%Y%m%d%H%M%S", time.localtime())) + ".xlsx"
    tips = "带表头的excel见："
    generate_result_file(result_file_name, new_book, tips)


def combine_excel(to_be_combined_dir, combined_sheets, start_row, excel_only_header):
    if start_row <= 0:
        print("出错了，start_row不能小于等于0")
        return
    excel_file_list_tmp = os.listdir(to_be_combined_dir)
    # 下面这段可用语法糖写简洁一些。不过暂先这样吧。
    excel_file_list = []
    for tmp in excel_file_list_tmp:
        if tmp.endswith("xlsx") and not tmp.startswith("~"):
            excel_file_list.append(to_be_combined_dir + os.path.sep + tmp)

    sheet_name_list = combined_sheets.split(SPLIT_STR)

    new_book = openpyxl.Workbook()
    total_row_dict = {}

    for sheet_name in sheet_name_list:
        new_sheet_start_row = 1
        new_sheet = new_book.create_sheet(sheet_name)
        for excel_file in excel_file_list:
            print("处理excel " + excel_file + "\nsheet " + sheet_name)
            source_book = openpyxl.load_workbook(excel_file)
            # TODO不存在的sheet_name要做一下是否存在判断
            source_sheet = source_book[sheet_name]
            row_count = source_sheet.max_row
            col_count = source_sheet.max_column
            copied_row_cnt = row_count - start_row + 1
            print("拷贝的数据行数：" + str(copied_row_cnt) + "，列数: " + str(col_count))
            total_row_dict[sheet_name] = total_row_dict.setdefault(sheet_name, 0) + copied_row_cnt
            for i in range(start_row, row_count + 1):
                for j in range(1, col_count + 1):
                    new_sheet.cell(new_sheet_start_row, j, source_sheet.cell(i, j).value)
                new_sheet_start_row = new_sheet_start_row + 1
    print("------------------------")
    print("各sheet共捕获数据行数如下：")
    print(total_row_dict)

    result_file_name = "output/merge_result_" + str(
        time.strftime("%Y%m%d%H%M%S", time.localtime())) + "_without_header" + ".xlsx"
    tips = "本工具目前版本，合并后的excel不携带原样式。\n生成的不带表头的excel见："
    generate_result_file(result_file_name, new_book, tips)
    generate_excel_with_header(excel_only_header, result_file_name, sheet_name_list)


# 查找指定文件夹下的excel文件，递归查找该文件夹下的子文件夹
# 使用xlrd可处理的excel文件后缀为xls xlsx
def find_in_excel_recursion(path, target_str, n=1):
    files = os.listdir(path)
    for file in files:
        file_path = os.path.join(path, file)
        if os.path.isdir(file_path):
            find_in_excel_recursion(file_path, target_str, n + 1)
        else:
            if (file.endswith("xlsx") or file.endswith("xls") or file.endswith("xlsm")) and not file.startswith("~"):
                excel_file = os.path.join(path, file)
                book = xlrd.open_workbook(excel_file)
                result_str = ""
                for s in range(len(book.sheets())):
                    sheet = book.sheet_by_index(s)
                    row_count = sheet.nrows
                    col_count = sheet.ncols
                    for i in range(row_count):
                        for j in range(col_count):
                            if target_str.upper() in str(sheet.cell_value(i, j)).upper():
                                # print(" sheet：" + sheet.name)
                                # print(" 单元格的值：" + sheet.cell_value(i, j))
                                # print("位置" + chr(65 + j) + str(i + 1)) 这句有bug，算出来的单元格位置不对哈
                                result_str += " sheet：" + sheet.name + "\n" + " 单元格的值：" + sheet.cell_value(i, j) + "\n"
                if len(result_str) > 0:
                    print("在这里找到啦：" + excel_file)
                    print(result_str)


def generate_csv(excel_file, csv_folder):
    book = openpyxl.load_workbook(excel_file)
    sheet_name_list = book.sheetnames
    print("您所选择的excel有如下sheet：")
    print(SPLIT_STR.join(sheet_name_list))
    for sheet_name in sheet_name_list:

        sheet = book[sheet_name]
        row_count = sheet.max_row
        col_count = sheet.max_column

        file_name = csv_folder + os.path.sep + os.path.basename(excel_file).split(".")[0] + "_" + sheet_name + ".csv"
        with open(file_name, "w", encoding="utf-8") as w:
            for i in range(1, row_count + 1):
                for j in range(1, col_count + 1):
                    w.write(str(sheet.cell(i, j).value))
                    if j != col_count:
                        w.write("\t")
                if i != row_count:
                    w.write("\n")
        print("生成csv文件：" + os.path.abspath(file_name))


def generate_result_file(result_file_name, work_book, tips):
    if os.path.exists("output") == False:
        print("output文件夹不存在，现在创建它")
        os.mkdir("output")
    work_book.save(result_file_name)
    print(tips)
    print("生成的文件见：")
    print(os.path.abspath(result_file_name))


if __name__ == '__main__':
    print("hello")
